﻿

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[proc_Statistics_Financial_CreateDaily_aotu_getlist20150109]
	-- Add the parameters for the stored procedure here
	(
	@CompanyId INT ,
	@TDate	DATETIME,
	@userid varchar(10)
	)
AS
BEGIN
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime, Convert(varchar(10),@TDate,120) + ' 00:00:00')
	Set @eRq = Convert(Datetime, Convert(varchar(10),@TDate,120) + ' 23:59:59')
	declare @f INT,@companyname nvarchar(200)
	declare @n1 DECIMAL(18,2),@n2  DECIMAL(18,2),@n3  DECIMAL(18,2)

--先生成当日现金银行明细
exec [dbo].[proc_Statistics_Financial_YHXJ_ByCompanyId] @CompanyId

--查询生成的结果

SELECT 
@n1=(
--当日实有现金
select sum(ye) from bankye where companyid=@CompanyId and flag=1 and [date] between @sRq and @eRq
) --AS n1
,
@n2=(
--当日实有银行	
select sum(ye) from bankye where companyid=@CompanyId and flag=2 and [date] between @sRq and @eRq
) --AS n2
,
@n3=(

--账户余额
select
isnull((SELECT SUM(ye) AS ye FROM (
SELECT (  SELECT  TOP 1 ye From AB_List c Where c.CompanyId=@CompanyId And AB_Type=0 AND c.Iid =ai.id AND
   c.UpTime<=@eRq 
   Order By c.UpTime DESC) AS ye 

FROM AB_Interface ai WHERE  syzh<>0 AND ai.CompanyId=@CompanyId) AS a),0)+
--对冲
ISNULL((SELECT sum(dch_zhh) FROM Order_Change_Hedge WHERE  CompanyId=@CompanyId  AND UpTime>=@sRq and UpTime<=@eRq),0) 
	 
) --AS n3

select  @f=count(1) from Statistics_Financial where Statistics_Financial.companyid=@CompanyId  and Statistics_Financial.createdate between @sRq and @eRq
select @companyname=companyname from company where id=@CompanyId
if @f=1
begin
	update Statistics_Financial set IBA=@n1,IBB=@n2,IBC=@n3,companyname=@companyname,createuser=@userid,createdate=getdate()
  where Statistics_Financial.companyid=@CompanyId 
	and Statistics_Financial.createdate between @sRq and @eRq
 end
 else
 begin
 insert into Statistics_Financial(iba,ibb,ibc,createdate,companyid,companyname,createuser)
 values( @n1,@n2,@n3,getdate(),@CompanyId ,@companyname,@userid )
end
 select @n1 as n1,@n2 as n2,@n3 as n3
 

END



